/**
 * 设备评价数据库模型
 * model/sqlite/deviceFeedback.js
 */

const sqlite3 = require("sqlite3").verbose();
const config = require("../../common/config");
const util = require("../../common/util");

class DeviceFeedbackDB  {
  constructor() {
    this.dbFile = config.dbfile;
    this.instance = null;
    this.db = null;
  }

  static getInstance() {
    if (!this.instance) {
      this.instance = new DeviceFeedbackDB();
    }
    return this.instance;
  }

  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) reject(err);
        else resolve("connect ok.");
      });
    });
  }

  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) reject(err);
        else resolve("close ok.");
      });
    });
  }

  /**
   * 获取设备评价列表
   * @param {number} deviceId - 设备ID
   * @param {number} currentUserId - 当前用户ID (用于判断是否点赞/踩)
   * @param {number} limit - 限制数量
   * @param {number} offset - 偏移量
   * @returns {Promise<Array>} 评价列表
   */
  async getFeedbackList(deviceId, currentUserId, limit = -1, offset = -1) {
    try {
      let sql = `
        SELECT 
          f.id, 
          f.device_id, 
          f.user_id, 
          f.rating, 
          f.feedback, 
          f.created_time,
          u.username as user_name,
          u.nickname as user_nickname,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND type = 'like') as like_count,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND type = 'dislike') as dislike_count,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND user_id = ? AND type = 'like') as is_liked,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND user_id = ? AND type = 'dislike') as is_disliked
        FROM device_feedback f
        LEFT JOIN users u ON f.user_id = u.id
        WHERE f.device_id = ?
        ORDER BY f.created_time DESC
      `;
      
      const params = [currentUserId, currentUserId, deviceId];
      
      if (limit > 0) {
        sql += " LIMIT ?";
        params.push(limit);
      }
      
      if (offset > 0) {
        sql += " OFFSET ?";
        params.push(offset);
      }
      
      console.log('Executing SQL:', sql);
      console.log('With params:', params);
      
      return new Promise((resolve, reject) => {
        this.db.all(sql, params, (err, rows) => {
          if (err) {
            console.error('SQL Error:', err);
            reject(err);
          } else {
            console.log('Query result:', rows);
            resolve(rows);
          }
        });
      });
    } catch (err) {
      console.error('获取设备评价列表失败:', err);
      throw err;
    }
  }

  /**
   * 获取设备平均评分
   * @param {number} deviceId - 设备ID
   * @returns {Promise<Object>} 平均评分和总评分数
   */
  async getAverageRating(deviceId) {
    try {
      const sql = `
        SELECT AVG(rating) as average_rating, COUNT(*) as total_ratings
        FROM device_feedback
        WHERE device_id = ?
      `;
      
      console.log('Executing SQL:', sql);
      console.log('With params:', [deviceId]);
      
      return new Promise((resolve, reject) => {
        this.db.get(sql, [deviceId], (err, row) => {
          if (err) {
            console.error('SQL Error:', err);
            reject(err);
          } else {
            console.log('Query result:', row);
            resolve({
              averageRating: row ? row.average_rating || 0 : 0,
              totalRatings: row ? row.total_ratings || 0 : 0
            });
          }
        });
      });
    } catch (err) {
      console.error('获取设备平均评分失败:', err);
      throw err;
    }
  }

  /**
   * 添加设备评价
   * @param {number} deviceId - 设备ID
   * @param {number} userId - 用户ID
   * @param {number} rating - 评分
   * @param {string} feedback - 评价内容
   * @returns {Promise<number>} 评价ID
   */
  async addFeedback(deviceId, userId, rating, feedback) {
    try {
      const sql = `
        INSERT INTO device_feedback (device_id, user_id, rating, feedback, created_time)
        VALUES (?, ?, ?, ?, datetime('now'))
      `;
      
      console.log('Executing SQL:', sql);
      console.log('With params:', [deviceId, userId, rating, feedback]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [deviceId, userId, rating, feedback], function(err) {
          if (err) {
            console.error('SQL Error:', err);
            reject(err);
          } else {
            console.log('Insert result:', this);
            resolve(this.lastID);
          }
        });
      });
    } catch (err) {
      console.error('添加设备评价失败:', err);
      throw err;
    }
  }

  /**
   * 点赞评价
   * @param {number} feedbackId - 评价ID
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 是否点赞成功
   */
  async likeFeedback(feedbackId, userId) {
    try {
      const sql = `
        INSERT INTO feedback_likes (feedback_id, user_id, type)
        VALUES (?, ?, 'like')
      `;
      
      console.log('Executing SQL:', sql);
      console.log('With params:', [feedbackId, userId]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [feedbackId, userId], function(err) {
          if (err) {
            if (err.code === 'SQLITE_CONSTRAINT') {
              // 已经点赞过，取消点赞
              this.unlikeFeedback(feedbackId, userId, 'like')
                .then(() => resolve(false))
                .catch(reject);
            } else {
              console.error('SQL Error:', err);
              reject(err);
            }
          } else {
            resolve(true);
          }
        }.bind(this));
      });
    } catch (err) {
      console.error('点赞评价失败:', err);
      throw err;
    }
  }

  /**
   * 踩评价
   * @param {number} feedbackId - 评价ID
   * @param {number} userId - 用户ID
   * @returns {Promise<boolean>} 是否踩成功
   */
  async dislikeFeedback(feedbackId, userId) {
    try {
      const sql = `
        INSERT INTO feedback_likes (feedback_id, user_id, type)
        VALUES (?, ?, 'dislike')
      `;
      
      console.log('Executing SQL:', sql);
      console.log('With params:', [feedbackId, userId]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [feedbackId, userId], function(err) {
          if (err) {
            if (err.code === 'SQLITE_CONSTRAINT') {
              // 已经踩过，取消踩
              this.unlikeFeedback(feedbackId, userId, 'dislike')
                .then(() => resolve(false))
                .catch(reject);
            } else {
              console.error('SQL Error:', err);
              reject(err);
            }
          } else {
            resolve(true);
          }
        }.bind(this));
      });
    } catch (err) {
      console.error('踩评价失败:', err);
      throw err;
    }
  }

  /**
   * 取消点赞/踩
   * @param {number} feedbackId - 评价ID
   * @param {number} userId - 用户ID
   * @param {string} type - 类型：'like' 或 'dislike'
   * @returns {Promise<boolean>} 是否取消成功
   */
  async unlikeFeedback(feedbackId, userId, type) {
    try {
      const sql = `
        DELETE FROM feedback_likes
        WHERE feedback_id = ? AND user_id = ? AND type = ?
      `;
      
      console.log('Executing SQL:', sql);
      console.log('With params:', [feedbackId, userId, type]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [feedbackId, userId, type], function(err) {
          if (err) {
            console.error('SQL Error:', err);
            reject(err);
          } else {
            resolve(true);
          }
        });
      });
    } catch (err) {
      console.error('取消操作失败:', err);
      throw err;
    }
  }

  /**
   * 获取所有设备评价列表（含设备信息和用户信息）
   * @param {number} limit - 限制数量
   * @param {number} offset - 偏移量
   * @param {string} orderBy - 排序字段
   * @param {string} sort - 排序方式 (asc/desc)
   * @returns {Promise<Array>} 评价列表
   */
  async getAllFeedbackWithDeviceInfo(limit = 10, offset = 0, orderBy = 'created_time', sort = 'desc') {
    try {
      // Ensure orderBy is a valid column to prevent SQL injection
      const validOrderBy = ['f.created_time', 'f.id', 'd.title', 'u.username', 'f.rating'];
      if (!validOrderBy.includes(orderBy)) {
        orderBy = 'f.created_time'; // Default to created_time if invalid
      }
      // Ensure sort is valid
      const validSort = ['asc', 'desc'];
      if (!validSort.includes(sort.toLowerCase())) {
        sort = 'desc'; // Default to desc if invalid
      }

      let sql = `
        SELECT
          f.id,
          f.device_id,
          f.user_id,
          f.rating,
          f.feedback,
          f.created_time,
          d.title as device_name,
          u.username as user_name,
          u.nickname as user_nickname
        FROM device_feedback f
        LEFT JOIN lab_device d ON f.device_id = d.id
        LEFT JOIN users u ON f.user_id = u.id
        ORDER BY ${orderBy} ${sort}
      `;

      const params = [];

      if (limit > 0) {
        sql += " LIMIT ?";
        params.push(limit);
      }

      if (offset >= 0) { // Offset can be 0
        sql += " OFFSET ?";
        params.push(offset);
      }

      console.log('Executing SQL for getAllFeedbackWithDeviceInfo:', sql);
      console.log('With params:', params);

      return new Promise((resolve, reject) => {
        this.db.all(sql, params, (err, rows) => {
          if (err) {
            console.error('SQL Error in getAllFeedbackWithDeviceInfo:', err);
            reject(err);
          } else {
            console.log('Query result for getAllFeedbackWithDeviceInfo:', rows);
            resolve(rows);
          }
        });
      });
    } catch (err) {
      console.error('获取所有设备评价列表失败:', err);
      throw err;
    }
  }

  /**
   * 获取所有设备评价的总数
   * @returns {Promise<number>} 评论总数
   */
  async getAllFeedbackCount() {
    try {
      const sql = `
        SELECT COUNT(*) as total
        FROM device_feedback
      `;

      console.log('Executing SQL for getAllFeedbackCount:', sql);

      return new Promise((resolve, reject) => {
        this.db.get(sql, (err, row) => {
          if (err) {
            console.error('SQL Error in getAllFeedbackCount:', err);
            reject(err);
          } else {
            console.log('Query result for getAllFeedbackCount:', row);
            resolve(row ? row.total : 0);
          }
        });
      });
    } catch (err) {
      console.error('获取所有设备评价总数失败:', err);
      throw err;
    }
  }

  /**
   * 根据ID获取单个评价
   * @param {number} feedbackId - 评价ID
   * @returns {Promise<Object>} 单个评价对象
   */
  async getFeedbackById(feedbackId) {
    try {
      const sql = `
        SELECT 
          f.id, 
          f.device_id, 
          f.user_id, 
          f.rating, 
          f.feedback, 
          f.created_time,
          d.title as device_name,
          u.username as user_name,
          u.nickname as user_nickname
        FROM device_feedback f
        LEFT JOIN lab_device d ON f.device_id = d.id
        LEFT JOIN users u ON f.user_id = u.id
        WHERE f.id = ?
      `;
      
      console.log('Executing SQL for getFeedbackById:', sql);
      console.log('With params:', [feedbackId]);
      
      return new Promise((resolve, reject) => {
        this.db.get(sql, [feedbackId], (err, row) => {
          if (err) {
            console.error('SQL Error in getFeedbackById:', err);
            reject(err);
          } else {
            console.log('Query result for getFeedbackById:', row);
            resolve(row);
          }
        });
      });
    } catch (err) {
      console.error('根据ID获取评价失败:', err);
      throw err;
    }
  }

  /**
   * 更新评价
   * @param {number} feedbackId - 评价ID
   * @param {string} feedbackContent - 评论内容
   * @param {number} ratingValue - 评分
   * @returns {Promise<boolean>} 更新是否成功
   */
  async updateFeedback(feedbackId, feedbackContent, ratingValue) {
    try {
      const sql = `
        UPDATE device_feedback
        SET feedback = ?, rating = ?, created_time = datetime('now')
        WHERE id = ?
      `;
      
      console.log('Executing SQL for updateFeedback:', sql);
      console.log('With params:', [feedbackContent, ratingValue, feedbackId]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [feedbackContent, ratingValue, feedbackId], function(err) {
          if (err) {
            console.error('SQL Error in updateFeedback:', err);
            reject(err);
          } else {
            console.log('Update result:', this);
            resolve(this.changes > 0); // changes is the number of rows changed
          }
        });
      });
    } catch (err) {
      console.error('更新评价失败:', err);
      throw err;
    }
  }

  /**
   * 删除评价
   * @param {number} feedbackId - 评价ID
   * @returns {Promise<boolean>} 删除是否成功
   */
  async deleteReview(feedbackId) {
    try {
      const sql = `
        DELETE FROM device_feedback
        WHERE id = ?
      `;
      
      console.log('Executing SQL for deleteReview:', sql);
      console.log('With params:', [feedbackId]);
      
      return new Promise((resolve, reject) => {
        this.db.run(sql, [feedbackId], function(err) {
          if (err) {
            console.error('SQL Error in deleteReview:', err);
            reject(err);
          } else {
            console.log('Delete result:', this);
            resolve(this.changes > 0); // changes is the number of rows changed
          }
        });
      });
    } catch (err) {
      console.error('删除评价失败:', err);
      throw err;
    }
  }

  /**
   * 获取指定用户的评论列表
   * @param {number} userId - 用户ID
   * @param {number} limit - 限制数量
   * @param {number} offset - 偏移量
   * @returns {Promise<Array>} 评论列表
   */
  async getUserFeedback(userId, limit = 10, offset = 0) {
    try {
      let sql = `
        SELECT 
          f.id, 
          f.device_id, 
          f.user_id, 
          f.rating, 
          f.feedback, 
          f.created_time,
          d.title as device_name,
          u.username as user_name,
          u.nickname as user_nickname,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND type = 'like') as like_count,
          (SELECT COUNT(*) FROM feedback_likes WHERE feedback_id = f.id AND type = 'dislike') as dislike_count
        FROM device_feedback f
        LEFT JOIN lab_device d ON f.device_id = d.id
        LEFT JOIN users u ON f.user_id = u.id
        WHERE f.user_id = ?
        ORDER BY f.created_time DESC
      `;
      
      const params = [userId];
      
      if (limit > 0) {
        sql += " LIMIT ?";
        params.push(limit);
      }
      
      if (offset >= 0) {
        sql += " OFFSET ?";
        params.push(offset);
      }
      
      console.log('Executing SQL for getUserFeedback:', sql);
      console.log('With params:', params);
      
      return new Promise((resolve, reject) => {
        this.db.all(sql, params, (err, rows) => {
          if (err) {
            console.error('SQL Error in getUserFeedback:', err);
            reject(err);
          } else {
            console.log('Query result for getUserFeedback:', rows);
            resolve(rows);
          }
        });
      });
    } catch (err) {
      console.error('获取用户评论列表失败:', err);
      throw err;
    }
  }

  /**
   * 获取指定用户的评论总数
   * @param {number} userId - 用户ID
   * @returns {Promise<number>} 评论总数
   */
  async getUserFeedbackCount(userId) {
    try {
      const sql = `
        SELECT COUNT(*) as total
        FROM device_feedback
        WHERE user_id = ?
      `;
      
      console.log('Executing SQL for getUserFeedbackCount:', sql);
      console.log('With params:', [userId]);
      
      return new Promise((resolve, reject) => {
        this.db.get(sql, [userId], (err, row) => {
          if (err) {
            console.error('SQL Error in getUserFeedbackCount:', err);
            reject(err);
          } else {
            console.log('Query result for getUserFeedbackCount:', row);
            resolve(row ? row.total : 0);
          }
        });
      });
    } catch (err) {
      console.error('获取用户评论总数失败:', err);
      throw err;
    }
  }
}

module.exports = DeviceFeedbackDB; 